package com.xpc.controller.utils;

import com.xpc.pojo.Account;
import com.xpc.pojo.qin.ListUserAndAccount;
import com.xpc.pojo.User;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.sql.Timestamp;

/**
 * @author Qinkuer
 * @version 1.0.0
 * @ClassName ReadExcelInfo.java
 * @Description 用于读取excel文件数据,转换成User和Account.
 *              默认Excel表列名依次为 账号,姓名,性别,密码,任职情况.
 * @createTime 2022年05月14日 18:11:00
 */
public class ReadExcelInfo {

    public static ListUserAndAccount getUserInfo(MultipartFile file,Long minUserId) throws IOException, InvalidFormatException,NumberFormatException {
        if(file==null){
            throw new IOException("file is null");
        }
        // 读取整个Excel
        XSSFWorkbook sheets = new XSSFWorkbook(file.getInputStream());  //如果FileItem可用的话,可以直接从FileItem获取输入流
//        XSSFWorkbook sheets = new XSSFWorkbook(new File("D:\\GiteeProject\\curriculum_design\\UniversityGradeAdministrationSystem\\src\\test\\java\\com\\xpc\\adminTest\\testfile\\UserInformation.xlsx"));

        // 获取第一个表单Sheet
        XSSFSheet sheetAt = sheets.getSheetAt(0);
        notNull(sheetAt);
        ListUserAndAccount listUserAndAccount=new ListUserAndAccount();
        //默认第一行为标题行，i = 0
        // 循环获取每一行数据
        minUserId++;
        for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++,minUserId++) {
            XSSFRow row = sheetAt.getRow(i);

            String accountId = row.getCell(0).getStringCellValue();
            String name = row.getCell(1).getStringCellValue();
            Integer gender,positionState;
            try {
               gender = Integer.valueOf(row.getCell(2).getStringCellValue());
               positionState = Integer.valueOf(row.getCell(4).getStringCellValue());
            }catch (NumberFormatException e){
                //后端控制台将不会打印这个异常
                throw new NumberFormatException(e.getMessage()+" Excle表格Gender或PositionState列 第"+(i+1)+"行元素输入异常");
            }
            String password = row.getCell(3).getStringCellValue();
            //排除异常输入
            gender = gender<0||gender>1?1:gender;
            positionState=positionState>3||positionState<0?0:positionState;


            Account account = new Account();
            account.setAccountId(accountId);
            account.setUserId(minUserId);
            account.setCreatDate(new Timestamp(new java.util.Date().getTime()));

            User user = new User();
            user.setName(name);
            user.setNickname(name);
            user.setSalt(SaltGeneration.getSalt());
            user.setPassword(MD5AddSaltToEncrypt.encodeBySalt(password,user.getSalt()));
            user.setGender(gender);
            user.setPositionState(positionState);
            user.setUserId(minUserId);

            listUserAndAccount.AddUserAndAccount(user,account);
        }

        return listUserAndAccount;
    }

    private static void notNull(XSSFSheet sheetAt) throws IOException {
        for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++){
            XSSFRow row = sheetAt.getRow(i);
            // 读取每一格内容
            for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
                XSSFCell cell = row.getCell(index);
                // cell.setCellType(XSSFCell.CELL_TYPE_STRING); 过期，使用下面替换
                cell.setCellType(CellType.STRING);
                if (cell.getStringCellValue().equals("")) {
                    throw new IOException("存在空值,请修改后,重新导入!");
                }
            }
        }
    }

}
